from __future__ import annotations

"""
交易执行管道 API
---------------
E3 阶段：提供只读接口/看板
"""

import datetime as dt
from decimal import Decimal
from typing import List, Optional

from fastapi import APIRouter, Depends, HTTPException, Query
from pydantic import BaseModel, Field
from sqlalchemy import select, func, and_, or_
from sqlalchemy.ext.asyncio import AsyncSession
import logging

from ..db import get_session
from ..deps import require_admin

logger = logging.getLogger(__name__)
from ..models import (
    Decision,
    DecisionStatus,
    Fill,
    PnLDaily,
    Run,
    RunMode,
    Snapshot,
    TradingOrder,
)

router = APIRouter(
    prefix="/trading",
    tags=["trading"],
    dependencies=[Depends(require_admin)],
)


# ========== 响应模型 ==========

class RunOverviewResp(BaseModel):
    """运行总览响应"""
    run_id: str
    model: str
    mode: RunMode
    started_at: dt.datetime
    latest_action: Optional[dict] = None
    daily_pnl: Optional[dict] = None


class DecisionItemResp(BaseModel):
    """决策项响应"""
    id: int
    run_id: str
    ts: dt.datetime
    action: str
    status: str
    reason: Optional[str] = None
    reason_detail: Optional[dict] = Field(
        default=None,
        description="详细理由，包含规划器与风控侧的说明"
    )
    result: Optional[dict] = None
    debug_trace: Optional[str] = Field(
        default=None,
        description="AI 推理过程原文"
    )


class OrderWithFillsResp(BaseModel):
    """订单及成交响应"""
    order: dict
    fills: List[dict]


# ========== E3.1: 运行总览 ==========

@router.get("/overview", response_model=RunOverviewResp)
async def get_run_overview(
    run_id: Optional[str] = Query(None, description="运行ID，为空时返回最新"),
    session: AsyncSession = Depends(get_session),
) -> RunOverviewResp:
    """获取运行总览：最近动作、当日 PnL、模式、模型名"""
    if run_id:
        stmt = select(Run).where(Run.run_id == run_id)
    else:
        stmt = select(Run).order_by(Run.started_at.desc()).limit(1)
    
    result = await session.execute(stmt)
    run = result.scalar_one_or_none()
    
    if not run:
        raise HTTPException(status_code=404, detail="Run 不存在")
    
    # 获取最近动作
    latest_decision_stmt = (
        select(Decision)
        .where(Decision.run_id == run.run_id)
        .order_by(Decision.ts.desc())
        .limit(1)
    )
    latest_decision_result = await session.execute(latest_decision_stmt)
    latest_decision = latest_decision_result.scalar_one_or_none()
    
    latest_action = None
    if latest_decision:
        # status 可能是枚举或字符串，需要处理
        status_value = latest_decision.status.value if hasattr(latest_decision.status, 'value') else latest_decision.status
        latest_action = {
            "action": latest_decision.action_json.get("action"),
            "status": status_value,
            "ts": latest_decision.ts,
        }
    
    # 获取当日 PnL
    today = dt.date.today()
    pnl_stmt = select(PnLDaily).where(
        and_(PnLDaily.run_id == run.run_id, PnLDaily.day == today)
    )
    pnl_result = await session.execute(pnl_stmt)
    pnl = pnl_result.scalar_one_or_none()
    
    daily_pnl = None
    if pnl:
        daily_pnl = {
            "realized": float(pnl.realized),
            "unrealized": float(pnl.unrealized),
            "day": pnl.day.isoformat(),
        }
    
    # mode 可能是枚举或字符串，需要转换为枚举
    mode_value = run.mode
    if isinstance(mode_value, str):
        try:
            mode_value = RunMode(mode_value)
        except ValueError:
            # 如果无法转换，使用默认值
            mode_value = RunMode.shadow
    
    return RunOverviewResp(
        run_id=run.run_id,
        model=run.model,
        mode=mode_value,
        started_at=run.started_at,
        latest_action=latest_action,
        daily_pnl=daily_pnl,
    )


# ========== E3.2: 决策流水 ==========

@router.get("/decisions", response_model=List[DecisionItemResp])
async def list_decisions(
    start_time: Optional[dt.datetime] = Query(None, description="开始时间"),
    end_time: Optional[dt.datetime] = Query(None, description="结束时间"),
    run_id: Optional[str] = Query(None, description="运行ID"),
    status: Optional[DecisionStatus] = Query(None, description="状态筛选"),
    limit: int = Query(100, ge=1, le=1000, description="返回数量"),
    session: AsyncSession = Depends(get_session),
) -> List[DecisionItemResp]:
    """查询决策流水
    
    优化说明：
    - 使用索引优化查询（idx_decisions_run_ts 或 idx_decisions_status_ts）
    - 只选择必要字段，减少数据传输
    - 使用 PostgreSQL JSONB 操作符直接提取 action，避免加载整个 JSON
    """
    try:
        # 优化：只选择需要的字段，减少数据传输
        # 使用 func.jsonb_extract_path_text 直接从 JSONB 中提取 action，避免加载整个对象
        from sqlalchemy import func as sql_func
        
        stmt = select(
            Decision.id,
            Decision.run_id,
            Decision.ts,
            Decision.status,
            Decision.reason,
            Decision.result,
            # 直接从 JSONB 提取 action 与 reason 字段，避免反序列化整个对象
            sql_func.jsonb_extract_path_text(Decision.action_json, 'action').label('action'),
            sql_func.jsonb_extract_path_text(Decision.action_json, 'reason').label('planner_reason'),
            sql_func.jsonb_extract_path_text(Decision.action_json, 'debug_trace').label('debug_trace'),
        )
        
        conditions = []
        # 优化索引使用：如果有 run_id，使用 idx_decisions_run_ts
        if run_id:
            conditions.append(Decision.run_id == run_id)
        # 如果有 status，使用 idx_decisions_status_ts
        if status:
            if isinstance(status, DecisionStatus):
                status_str = status.value
            else:
                status_str = status
            conditions.append(Decision.status == status_str)
        # 时间范围条件
        if start_time:
            conditions.append(Decision.ts >= start_time)
        if end_time:
            conditions.append(Decision.ts <= end_time)
        
        if conditions:
            stmt = stmt.where(and_(*conditions))
        
        # 优化：确保使用索引进行排序
        # 如果有 run_id，使用 (run_id, ts DESC)；否则使用 (status, ts DESC) 或 (ts DESC)
        if run_id:
            # run_id 查询使用 idx_decisions_run_ts
            stmt = stmt.order_by(Decision.ts.desc()).limit(limit)
        elif status:
            # status 查询使用 idx_decisions_status_ts
            stmt = stmt.order_by(Decision.ts.desc()).limit(limit)
        else:
            # 需要添加 ts 索引（如果还没有的话）
            stmt = stmt.order_by(Decision.ts.desc()).limit(limit)
        
        result = await session.execute(stmt)
        rows = result.all()
        
        # 构建响应
        decisions = []
        for row in rows:
            planner_reason_raw = row.planner_reason
            risk_reason_raw = row.reason

            planner_reason = planner_reason_raw.strip() if planner_reason_raw else None
            risk_reason = risk_reason_raw.strip() if risk_reason_raw else None

            # 合并风控侧与规划器侧的理由，提供更多上下文
            if planner_reason and risk_reason:
                if planner_reason == risk_reason:
                    combined_reason = planner_reason
                else:
                    combined_reason = f"规划器: {planner_reason}; 风控: {risk_reason}"
            elif planner_reason:
                combined_reason = f"规划器: {planner_reason}"
            else:
                combined_reason = risk_reason

            detail_dict = {}
            if planner_reason:
                detail_dict["planner"] = planner_reason
            if risk_reason:
                detail_dict["risk_gate"] = risk_reason
            reason_detail = detail_dict or None

            decisions.append(
                DecisionItemResp(
                    id=row.id,
                    run_id=row.run_id,
                    ts=row.ts,
                    action=row.action if row.action else "UNKNOWN",  # 从 JSONB 直接提取的 action
                    status=row.status.value if hasattr(row.status, 'value') else row.status,
                    reason=combined_reason,
                    reason_detail=reason_detail,
                    result=row.result,
                    debug_trace=row.debug_trace,
                )
            )
        
        return decisions
        
    except Exception as e:
        logger.error(f"查询决策流水失败: {e}", exc_info=True)
        raise HTTPException(status_code=500, detail=f"查询失败: {str(e)}")


# ========== E3.3: 订单/成交明细 ==========

@router.get("/orders", response_model=List[OrderWithFillsResp])
async def list_orders_with_fills(
    start_time: Optional[dt.datetime] = Query(None, description="开始时间"),
    end_time: Optional[dt.datetime] = Query(None, description="结束时间"),
    exchange: Optional[str] = Query(None, description="交易所"),
    symbol: Optional[str] = Query(None, description="交易对"),
    run_id: Optional[str] = Query(None, description="运行ID"),
    limit: int = Query(100, ge=1, le=1000, description="返回数量"),
    session: AsyncSession = Depends(get_session),
) -> List[OrderWithFillsResp]:
    """查询订单及成交明细"""
    stmt = select(TradingOrder)
    
    conditions = []
    if start_time:
        conditions.append(TradingOrder.created_at >= start_time)
    if end_time:
        conditions.append(TradingOrder.created_at <= end_time)
    if exchange:
        conditions.append(TradingOrder.exchange == exchange)
    if symbol:
        conditions.append(TradingOrder.symbol == symbol)
    if run_id:
        conditions.append(TradingOrder.run_id == run_id)
    
    if conditions:
        stmt = stmt.where(and_(*conditions))
    
    stmt = stmt.order_by(TradingOrder.created_at.desc()).limit(limit)
    
    result = await session.execute(stmt)
    orders = result.scalars().all()
    
    # 查询每个订单的成交
    order_ids = [o.order_id for o in orders]
    
    fills_stmt = select(Fill).where(Fill.order_id.in_(order_ids))
    fills_result = await session.execute(fills_stmt)
    all_fills = fills_result.scalars().all()
    
    # 按 order_id 分组
    fills_by_order = {}
    for fill in all_fills:
        if fill.order_id not in fills_by_order:
            fills_by_order[fill.order_id] = []
        fills_by_order[fill.order_id].append({
            "id": fill.id,
            "order_id": fill.order_id,
            "price": float(fill.price),
            "qty": float(fill.qty),
            "fee": float(fill.fee) if fill.fee else None,
            "fee_asset": fill.fee_asset,
            "ts": fill.ts,
        })
    
    return [
        OrderWithFillsResp(
            order={
                "order_id": o.order_id,
                "client_order_id": o.client_order_id,
                "run_id": o.run_id,
                "exchange": o.exchange,
                "symbol": o.symbol,
                "side": o.side,
                "order_type": o.order_type,
                "price": float(o.price) if o.price else None,
                "amount": float(o.amount),
                "status": o.status,
                "created_at": o.created_at,
            },
            fills=fills_by_order.get(o.order_id, []),
        )
        for o in orders
    ]


# ========== 调度器管理接口 ==========

class StartSchedulerReq(BaseModel):
    """启动调度器请求"""
    run_id: Optional[str] = Field(
        None, 
        description="运行ID，唯一标识。如果不提供，将自动生成",
        examples=["my-run-001"]
    )
    exchange: str = Field(
        ..., 
        description="交易所名称，如 'binance', 'okx', 'gate_io'",
        examples=["binance"]
    )
    symbol: str = Field(
        ..., 
        description="交易对符号，如 'BTC-USDT', 'ETH-USDT'",
        examples=["BTC-USDT"]
    )
    account_name: str = Field(
        ..., 
        description="账户名称，对应 Hummingbot 配置的账户",
        examples=["test@test.com", "master_account"]
    )
    risk_config: dict = Field(
        ..., 
        description="""
        风控配置（字典对象），包含以下字段：
        - allowed_exchanges: List[str] - 允许的交易所白名单，如 ["binance", "okx"]
        - allowed_symbols: List[str] - 允许的交易对白名单，如 ["BTC-USDT", "ETH-USDT"]
        - max_notional_usd: float - 最大名义金额（USD），如 1000.0
        - max_slippage_bps: int - 最大滑点（基点），50 表示 0.5%，如 50
        - max_daily_loss_usd: Optional[float] - 最大日亏损（USD），可选，如 300.0
        - enable_derivatives: bool - 是否允许衍生品交易，默认 true
        - data_sources: Optional[dict] - (可选) 数据来源覆盖，如：
            - price_connector: str - 获取价格的连接器，例如 "gate_io"
            - perp_connector: str - 获取资金费率/持仓的永续连接器，例如 "gate_io_perpetual"
            - balance_connectors: List[str] - 查询余额的连接器列表
            - positions_connectors: List[str] - 查询仓位的连接器列表
            - accounts: List[str] - 查询余额/仓位时要过滤的账户名
            - candles_connector: str - 获取 K 线的连接器，默认沿用 price_connector
            - candles_interval: str - K 线周期，默认 "10m"
            - candles_days: int - 历史天数，默认 7
            - trading_pair(s): str/List[str] - 强制指定行情交易对（默认使用 symbol）
        
        示例：
        {
          "allowed_exchanges": ["binance", "okx"],
          "allowed_symbols": ["BTC-USDT", "ETH-USDT"],
          "max_notional_usd": 1000.0,
          "max_slippage_bps": 50,
          "max_daily_loss_usd": 300.0,
          "enable_derivatives": true,
          "data_sources": {
            "price_connector": "okx",
            "perp_connector": "okx_perpetual",
            "balance_connectors": ["okx", "okx_perpetual"],
            "positions_connectors": ["okx_perpetual"],
            "candles_connector": "okx",
            "candles_interval": "10m",
            "candles_days": 7,
            "accounts": ["test@test.com"]
          }
        }
        """,
        examples=[{
            "allowed_exchanges": ["binance", "okx"],
            "allowed_symbols": ["BTC-USDT", "ETH-USDT"],
            "max_notional_usd": 1000.0,
            "max_slippage_bps": 50,
            "max_daily_loss_usd": 300.0,
            "enable_derivatives": True,
            "data_sources": {
                "price_connector": "okx",
                "perp_connector": "okx_perpetual",
                "balance_connectors": ["okx", "okx_perpetual"],
                "positions_connectors": ["okx_perpetual"],
                "candles_connector": "okx",
                "candles_interval": "10m",
                "candles_days": 7,
                "accounts": ["test@test.com"]
            }
        }]
    )
    mode: RunMode = Field(
        RunMode.shadow, 
        description="运行模式：shadow（影子，不执行）/ paper（模拟）/ live（实盘）",
        examples=["shadow"]
    )
    interval_seconds: int = Field(
        60, 
        ge=10, 
        le=3600, 
        description="""
        执行间隔（秒），范围：10-3600
        - 10秒：高频交易，测试用
        - 60秒：默认，每分钟执行一次
        - 300秒：5分钟，适合低频策略
        - 3600秒：1小时，适合长期持仓策略
        """,
        examples=[60]
    )


def generate_run_id(exchange: str, symbol: str, account_name: str) -> str:
    """自动生成唯一的 run_id
    
    格式：run-{exchange}-{symbol}-{timestamp}-{hash6}
    例如：run-binance-BTC-USDT-20250103-143022-a1b2c3
    """
    import time
    import hashlib
    
    timestamp = time.strftime("%Y%m%d-%H%M%S")
    # 使用 exchange, symbol, account_name, timestamp 生成 hash
    hash_input = f"{exchange}{symbol}{account_name}{timestamp}"
    hash6 = hashlib.md5(hash_input.encode()).hexdigest()[:6]
    
    return f"run-{exchange}-{symbol}-{timestamp}-{hash6}"


@router.post("/scheduler/start")
async def start_scheduler(
    req: StartSchedulerReq,
    session: AsyncSession = Depends(get_session),
) -> dict:
    """启动交易执行调度器
    
    如果未提供 run_id，系统会自动生成一个唯一的运行ID
    """
    from ..services.scheduler import start_scheduler
    
    # 如果未提供 run_id，自动生成
    run_id = req.run_id
    if not run_id:
        run_id = generate_run_id(req.exchange, req.symbol, req.account_name)
        logger.info(f"自动生成 run_id: {run_id}")
    
    scheduler = await start_scheduler(
        run_id=run_id,
        exchange=req.exchange,
        symbol=req.symbol,
        account_name=req.account_name,
        risk_config=req.risk_config,
        mode=req.mode,
        interval_seconds=req.interval_seconds,
    )
    
    return {
        "status": "started",
        "run_id": run_id,
        "message": "调度器已启动"
    }


@router.post("/scheduler/stop")
async def stop_scheduler(run_id: str = Query(..., description="运行ID")) -> dict:
    """停止调度器"""
    from ..services.scheduler import stop_scheduler
    
    await stop_scheduler(run_id)
    
    return {
        "status": "stopped",
        "run_id": run_id,
        "message": "调度器已停止"
    }


@router.post("/scheduler/reconcile")
async def trigger_reconcile(
    order_ids: Optional[List[str]] = Query(None, description="订单ID列表"),
    exchange: Optional[str] = Query(None, description="交易所"),
    session: AsyncSession = Depends(get_session),
) -> dict:
    """手动触发对账"""
    from ..services.reconciler import reconcile_orders
    
    count = await reconcile_orders(session, order_ids, exchange)
    
    return {
        "status": "success",
        "updated_orders": count
    }


@router.post("/scheduler/calculate-pnl")
async def trigger_calculate_pnl(
    day: Optional[dt.date] = Query(None, description="日期，默认今天"),
    run_id: Optional[str] = Query(None, description="运行ID"),
    session: AsyncSession = Depends(get_session),
) -> dict:
    """手动触发PnL计算"""
    from ..services.reconciler import calculate_daily_pnl
    
    await calculate_daily_pnl(session, day, run_id)
    
    return {
        "status": "success",
        "day": day.isoformat() if day else dt.date.today().isoformat(),
        "run_id": run_id
    }


@router.post("/scheduler/restore")
async def restore_schedulers(
    run_id: Optional[str] = Query(None, description="运行ID，为空则恢复所有"),
    session: AsyncSession = Depends(get_session),
) -> dict:
    """重启后恢复调度器
    
    从数据库读取调度器配置并重新启动。
    如果不提供 run_id，会恢复所有有完整配置的调度器。
    """
    from ..services.scheduler import start_scheduler
    from sqlalchemy import and_
    
    if run_id:
        stmt = select(Run).where(Run.run_id == run_id)
    else:
        # 查找所有有完整配置的 run（用于重启后恢复）
        stmt = select(Run).where(
            and_(
                Run.exchange.isnot(None),
                Run.symbol.isnot(None),
                Run.account_name.isnot(None),
                Run.risk_config.isnot(None),
                Run.interval_seconds.isnot(None),
            )
        )
    
    result = await session.execute(stmt)
    runs = result.scalars().all()
    
    restored = []
    failed = []
    
    for run in runs:
        try:
            # 验证必需字段
            if not run.exchange or not run.symbol or not run.account_name:
                failed.append({
                    "run_id": run.run_id,
                    "error": f"调度器配置不完整: exchange={run.exchange}, symbol={run.symbol}, account_name={run.account_name}"
                })
                logger.warning(f"跳过恢复调度器（配置不完整）: run_id={run.run_id}")
                continue
            
            # 确保 mode 是枚举
            if isinstance(run.mode, str):
                mode_enum = RunMode(run.mode)
            else:
                mode_enum = run.mode
            
            # 启动调度器
            scheduler = await start_scheduler(
                run_id=run.run_id,
                exchange=run.exchange,
                symbol=run.symbol,
                account_name=run.account_name,
                risk_config=run.risk_config or {},
                mode=mode_enum,
                interval_seconds=run.interval_seconds or 60,
            )
            restored.append(run.run_id)
            logger.info(f"调度器已恢复: run_id={run.run_id}")
        except Exception as e:
            failed.append({"run_id": run.run_id, "error": str(e)})
            logger.error(f"恢复调度器失败: run_id={run.run_id}, error={e}")
    
    return {
        "status": "completed",
        "restored": restored,
        "failed": failed,
        "total": len(runs),
        "message": f"成功恢复 {len(restored)} 个调度器"
    }


@router.get("/scheduler/list")
async def list_schedulers(
    session: AsyncSession = Depends(get_session),
) -> dict:
    """列出所有已配置的调度器（包括已停止的）"""
    stmt = select(Run).where(
        and_(
            Run.exchange.isnot(None),
            Run.symbol.isnot(None),
        )
    ).order_by(Run.started_at.desc())
    
    result = await session.execute(stmt)
    runs = result.scalars().all()
    
    from ..services.scheduler import _schedulers
    
    schedulers_info = []
    for run in runs:
        is_running = run.run_id in _schedulers
        schedulers_info.append({
            "run_id": run.run_id,
            "exchange": run.exchange,
            "symbol": run.symbol,
            "account_name": run.account_name,
            "mode": run.mode.value if hasattr(run.mode, 'value') else run.mode,
            "interval_seconds": run.interval_seconds,
            "started_at": run.started_at.isoformat(),
            "is_running": is_running,
            "has_config": run.risk_config is not None,
        })
    
    return {
        "schedulers": schedulers_info,
        "total": len(schedulers_info),
        "running": len(_schedulers)
    }
